﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.ApplicationBlocks.Data;
using System.Configuration;
using BE;
using BC;
using COMExcel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Threading;
using System.Data.SqlClient;

namespace ModuleDuoc
{
    public partial class frmTK_XuatHang : ModuleDuoc.Base.frmTK_Base
    {
        public frmTK_XuatHang()
        {
            InitializeComponent();
        }

        /// <summary>
        /// Form load
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void frmTK_XuatHang_Load(object sender, EventArgs e)
        {
            this.dgvdata.AutoGenerateColumns = false;
            this.dgvdatact.AutoGenerateColumns = false;
            //loadLoaiPhieu();
            this.cboLoaiPhieu.Text = "Tất cả";
            prgXuatExcel.Visible = false;
            this.lblText.Visible = false;
            cboBieuMau.SelectedIndex = 1;
        }

        /// <summary>
        /// Load loại phiếu
        /// </summary>
        private void loadLoaiPhieu()
        {
            string sql = "SELECT Id,Name FROM zD_LOAIPHIEU UNION select '',N'--Tất cả--'";
            DataTable dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                cboLoaiPhieu.DataSource = dt;
                cboLoaiPhieu.ValueMember = "Id";
                cboLoaiPhieu.DisplayMember = "Name";
            }
        }

        /// <summary>
        /// Lấy dữ liệu phiếu xuất kho
        /// </summary>
        private void getData()
        {
            string sql;

            if (this.cboLoaiPhieu.Text == "Tất cả")
            {
                sql = "SELECT * FROM zD_PHIEUXUATKHO zp WHERE zp.ngaylap BETWEEN '" + this.dateFrom.Value.ToString("MM/dd/yyyy HH:mm:ss") + "' AND '" + this.dateTo.Value.ToString("MM/dd/yyyy HH:mm:ss") + "' and huy=0 ORDER BY zp.ngaylap DESC";
            }
            else if (this.cboLoaiPhieu.Text == "Theo đơn")
            {
                sql = "SELECT * FROM zD_PHIEUXUATKHO zp WHERE zp.ngaylap BETWEEN '" + this.dateFrom.Value.ToString("MM/dd/yyyy HH:mm:ss") + "' AND '" + this.dateTo.Value.ToString("MM/dd/yyyy HH:mm:ss") + "' AND zp.DonThuocID IS NOT NULL ORDER BY zp.ngaylap DESC";
            }
            else //if (this.cboLoaiPhieu.Text == "Không theo đơn")
            {
                sql = "SELECT * FROM zD_PHIEUXUATKHO zp WHERE zp.ngaylap BETWEEN '" + this.dateFrom.Value.ToString("MM/dd/yyyy HH:mm:ss") + "' AND '" + this.dateTo.Value.ToString("MM/dd/yyyy HH:mm:ss") + "' AND zp.DonThuocID IS NULL ORDER BY zp.ngaylap DESC";
            }
            DataTable dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql).Tables[0];
            
            this.dgvdata.DataSource = dt;
            this.lblTongPhieuValue.Text = dt.Rows.Count.ToString();

            //string sql = "SELECT * FROM zD_PHIEUXUATKHO ";
            //if (cboLoaiPhieu.SelectedValue.ToString() != "")
            //{
            //    sql += " where loaiphieu='" + cboLoaiPhieu.SelectedValue.ToString() + "'";
            //}
            //sql += " order by id desc";
            //DataTable dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql).Tables[0];
            //if (dt.Rows.Count > 0)
            //{
            //    this.dgvdata.DataSource = dt;
            //    this.btnTongSoDon.Text = "Tổng số đơn: " + dt.Rows.Count.ToString();
            //}  
            float tongtien = 0;
            if(dt.Rows.Count>0)
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    tongtien += float.Parse(dt.Rows[i]["tongtien"].ToString());
                }
            this.lbl_TongTienValue.Text = String.Format("{0:0,0}", tongtien) + " VNĐ";
            this.lblBangChu.Text = DICHSO.XL_DOC_SO.DOC_SO_CHUOI(tongtien, "VNĐ", "", 0);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        public override void btnView_Click(object sender, EventArgs e)
        {
            base.btnView_Click(sender, e);
            getData();
        }

        public override void btnExit_Click(object sender, EventArgs e)
        {
            base.btnExit_Click(sender, e);
            this.Close();
        }

        private void dgvdata_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            string sql = "SELECT * FROM view_PHIEUXUATKHOCT where sophieu='" + dgvdata["sophieu", dgvdata.CurrentRow.Index].Value.ToString() + "'";
            DataTable dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                this.dgvdatact.DataSource = dt;

            }
        }

        public override void btnExcel_Click(object sender, EventArgs e)
        {
            System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

            base.btnExcel_Click(sender, e);
            this.prgXuatExcel.Visible = true;
            this.lblText.Visible = true;
            double progress = 0;
            //this.prgXuatExcel.Maximum = dgvData.Rows.Count;

            COMExcel.Application exApp = new COMExcel.Application();
            // Thêm file temp xls
            COMExcel.Workbook exBook = exApp.Workbooks.Add(
                      COMExcel.XlWBATemplate.xlWBATWorksheet);

            COMExcel.Worksheet exSheet = (COMExcel.Worksheet)exBook.Worksheets[1];
            exSheet.Name = "TONG_HOP_BAN_HANG";

            COMExcel.Range RE_TIEUDE = (COMExcel.Range)exSheet.get_Range("A1", "A1");
            RE_TIEUDE.Value2 = "Bệnh viện MEDLATEC";
            RE_TIEUDE.EntireRow.Font.Name = "Arial";
            RE_TIEUDE.EntireRow.Font.Size = 14;
            RE_TIEUDE.EntireRow.Font.Bold = true;
            exSheet.get_Range("A1", "I1").Merge(false);
            exSheet.get_Range("A1", "A1").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            COMExcel.Range RE_BC = (COMExcel.Range)exSheet.get_Range("A2", "A2");
            RE_BC.Value2 = "BẢNG TỔNG HỢP NHẬP THUỐC NGÀY 10 THÁNG 10 NĂM 2012";
            RE_BC.EntireRow.Font.Name = "Arial";
            RE_BC.EntireRow.Font.Size = 14;
            RE_BC.EntireRow.Font.Bold = true;
            exSheet.get_Range("A2", "A2").Merge(false);
            exSheet.get_Range("A2", "I2").Cells.HorizontalAlignment = COMExcel.Constants.xlCenter;

            COMExcel.Range HEAD_NHAP = (COMExcel.Range)exSheet.get_Range("C3", "C3");
            HEAD_NHAP.Value2 = "NGÀY :";
            HEAD_NHAP.EntireRow.Font.Name = "Arial";
            HEAD_NHAP.EntireRow.Font.Size = 10;
            HEAD_NHAP.EntireRow.Font.Bold = true;
            exSheet.get_Range("C3", "C3").Cells.HorizontalAlignment = COMExcel.Constants.xlRight;

            COMExcel.Range HEAD_NGUOIBAN = (COMExcel.Range)exSheet.get_Range("C4", "C4");
            HEAD_NGUOIBAN.Value2 = "Người bán :";
            HEAD_NGUOIBAN.EntireRow.Font.Name = "Arial";
            HEAD_NGUOIBAN.EntireRow.Font.Size = 10;
            HEAD_NGUOIBAN.EntireRow.Font.Bold = true;
            exSheet.get_Range("C4", "C4").Cells.HorizontalAlignment = COMExcel.Constants.xlRight;

            COMExcel.Range HEAD_SOTIENBANTHEOMAY = (COMExcel.Range)exSheet.get_Range("C5", "C5");
            HEAD_SOTIENBANTHEOMAY.Value2 = "Số tiền bán theo máy :";
            HEAD_SOTIENBANTHEOMAY.EntireRow.Font.Name = "Arial";
            HEAD_SOTIENBANTHEOMAY.EntireRow.Font.Size = 10;
            HEAD_SOTIENBANTHEOMAY.EntireRow.Font.Bold = true;
            exSheet.get_Range("C5", "C5").Cells.HorizontalAlignment = COMExcel.Constants.xlRight;


            COMExcel.Range HEAD_THUCTHU = (COMExcel.Range)exSheet.get_Range("C6", "C6");
            HEAD_THUCTHU.Value2 = "Thực tu tại quỹ :";
            HEAD_THUCTHU.EntireRow.Font.Name = "Arial";
            HEAD_THUCTHU.EntireRow.Font.Size = 10;
            HEAD_THUCTHU.EntireRow.Font.Bold = true;
            exSheet.get_Range("C6", "C6").Cells.HorizontalAlignment = COMExcel.Constants.xlRight;

            COMExcel.Range HEAD_GIAMGIA = (COMExcel.Range)exSheet.get_Range("G5", "G5");
            HEAD_GIAMGIA.Value2 = "Giảm giá :";
            HEAD_GIAMGIA.EntireRow.Font.Name = "Arial";
            HEAD_GIAMGIA.EntireRow.Font.Size = 10;
            HEAD_GIAMGIA.EntireRow.Font.Bold = true;
            exSheet.get_Range("G5", "G5").Cells.HorizontalAlignment = COMExcel.Constants.xlRight;

            COMExcel.Range HEAD_LAI = (COMExcel.Range)exSheet.get_Range("G6", "G6");
            HEAD_GIAMGIA.Value2 = "Lãi :";
            HEAD_GIAMGIA.EntireRow.Font.Name = "Arial";
            HEAD_GIAMGIA.EntireRow.Font.Size = 10;
            HEAD_GIAMGIA.EntireRow.Font.Bold = true;
            exSheet.get_Range("G6", "G6").Cells.HorizontalAlignment = COMExcel.Constants.xlRight;

            COMExcel.Range HEAD_LECHBAN = (COMExcel.Range)exSheet.get_Range("H4", "H4");
            HEAD_LECHBAN.Value2 = "Lệch bán :";
            HEAD_LECHBAN.EntireRow.Font.Name = "Arial";
            HEAD_LECHBAN.EntireRow.Font.Size = 10;
            HEAD_LECHBAN.EntireRow.Font.Bold = true;
            exSheet.get_Range("H4", "H4").Cells.HorizontalAlignment = COMExcel.Constants.xlRight;

            //Title
            COMExcel.Range STT = (COMExcel.Range)exSheet.get_Range("A8", "A8");
            STT.Value2 = "STT";
            STT.EntireRow.Font.Name = "Arial";
            STT.EntireRow.Font.Size = 10;
            STT.EntireRow.Font.Bold = true;

            COMExcel.Range MA = (COMExcel.Range)exSheet.get_Range("B8", "B8");
            MA.Value2 = "Mã";
            MA.EntireRow.Font.Name = "Arial";
            MA.EntireRow.Font.Size = 10;
            MA.EntireRow.Font.Bold = true;

            COMExcel.Range TENTHUOC = (COMExcel.Range)exSheet.get_Range("C8", "C8");
            TENTHUOC.Value2 = "Tên thuốc";
            TENTHUOC.EntireRow.Font.Name = "Arial";
            TENTHUOC.EntireRow.Font.Size = 10;
            TENTHUOC.EntireRow.Font.Bold = true;


            COMExcel.Range SOLUONG = (COMExcel.Range)exSheet.get_Range("D8", "D8");
            SOLUONG.Value2 = "SL";
            SOLUONG.EntireRow.Font.Name = "Arial";
            SOLUONG.EntireRow.Font.Size = 10;
            SOLUONG.EntireRow.Font.Bold = true;

            COMExcel.Range GIAMUA = (COMExcel.Range)exSheet.get_Range("E8", "E8");
            GIAMUA.Value2 = "Giá mua";
            GIAMUA.EntireRow.Font.Name = "Arial";
            GIAMUA.EntireRow.Font.Size = 10;
            GIAMUA.EntireRow.Font.Bold = true;


            COMExcel.Range GIABAN = (COMExcel.Range)exSheet.get_Range("F8", "F8");
            GIABAN.Value2 = "Giá bán";
            GIABAN.EntireRow.Font.Name = "Arial";
            GIABAN.EntireRow.Font.Size = 10;
            GIABAN.EntireRow.Font.Bold = true;

            COMExcel.Range GIAMGIA = (COMExcel.Range)exSheet.get_Range("G8", "G8");
            GIAMGIA.Value2 = "Giảm giá";
            GIAMGIA.EntireRow.Font.Name = "Arial";
            GIAMGIA.EntireRow.Font.Size = 10;
            GIAMGIA.EntireRow.Font.Bold = true;

            COMExcel.Range TONGBAN = (COMExcel.Range)exSheet.get_Range("H8", "H8");
            TONGBAN.Value2 = "Tổng bán";
            TONGBAN.EntireRow.Font.Name = "Arial";
            TONGBAN.EntireRow.Font.Size = 10;
            TONGBAN.EntireRow.Font.Bold = true;

            COMExcel.Range LAI = (COMExcel.Range)exSheet.get_Range("I8", "I8");
            LAI.Value2 = "Lãi";
            LAI.EntireRow.Font.Name = "Arial";
            LAI.EntireRow.Font.Size = 10;
            LAI.EntireRow.Font.Bold = true;


            int start = 9;
            DataTable dt = new DataTable();
            dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, "select * from zD_bcTonkhoth").Tables[0];
            this.prgXuatExcel.Maximum = dt.Rows.Count;
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    progress = i + 1;
                    // hien thi chay % tren thanh process, lam tron 2 ki tu
                    lblText.Text = Math.Round(((progress / dt.Rows.Count) * 100), 2).ToString() + "%";
                    prgXuatExcel.Value = i + 1;
                    Thread.Sleep(2);
                    Application.DoEvents();

                    //STT
                    COMExcel.Range ID_V = (COMExcel.Range)exSheet.get_Range("A" + (i + start), "A" + (i + start));
                    ID_V.Value2 = (i + 1).ToString();
                    ID_V.EntireColumn.AutoFit();

                    //MA
                    COMExcel.Range MA_V = (COMExcel.Range)exSheet.get_Range("B" + (i + start), "B" + (i + start));
                    MA_V.Value2 = dt.Rows[i]["mavt"].ToString();
                    MA_V.EntireColumn.AutoFit();

                    //Ten thuoc
                    COMExcel.Range TENTHUOC_V = (COMExcel.Range)exSheet.get_Range("C" + (i + start), "C" + (i + start));
                    TENTHUOC_V.Value2 = dt.Rows[i]["tenvt"].ToString();
                    TENTHUOC_V.EntireColumn.AutoFit();

                    //SL NHAP
                    COMExcel.Range SLNHAP_V = (COMExcel.Range)exSheet.get_Range("D" + (i + start), "D" + (i + start));
                    SLNHAP_V.Value2 = dt.Rows[i]["slnhap"].ToString();
                    SLNHAP_V.EntireColumn.AutoFit();

                    //GIANHAP
                    COMExcel.Range GIANHAP_V = (COMExcel.Range)exSheet.get_Range("E" + (i + start), "E" + (i + start));
                    GIANHAP_V.Value2 = "0";
                    GIANHAP_V.EntireColumn.AutoFit();

                    //GIABAN
                    COMExcel.Range GIABAN_V = (COMExcel.Range)exSheet.get_Range("F" + (i + start), "F" + (i + start));
                    GIABAN_V.Value2 = "0";
                    GIABAN_V.EntireColumn.AutoFit();

                    //GIAMGIA
                    COMExcel.Range GIAMGIA_V = (COMExcel.Range)exSheet.get_Range("G" + (i + start), "G" + (i + start));
                    GIAMGIA_V.Value2 = "0";
                    GIAMGIA_V.EntireColumn.AutoFit();

                    //TONGBAN
                    COMExcel.Range TONGBAN_V = (COMExcel.Range)exSheet.get_Range("H" + (i + start), "G" + (i + start));
                    TONGBAN_V.Value2 = "0";
                    TONGBAN_V.EntireColumn.AutoFit();

                    //LAI
                    COMExcel.Range LAI_V = (COMExcel.Range)exSheet.get_Range("I" + (i + start), "I" + (i + start));
                    LAI_V.Value2 = "0";
                    LAI_V.EntireColumn.AutoFit();
                }
                int countColumn = dt.Rows.Count + start;
                exSheet.get_Range("A8", "I8").Borders.ColorIndex = 1;
                exSheet.get_Range("A8", "I8").Interior.ColorIndex = 6;

                exSheet.get_Range("D8", "D8").Application.ActiveWindow.FreezePanes = true;
                exSheet.get_Range("A8", "I" + countColumn).Borders.ColorIndex = 1;
            #region SAVE FILE
            // Ẩn chương trình
            exApp.Visible = false;

            // Save file
            exBook.SaveAs("D:\\BC_XUATHANG_DUOC.xls", COMExcel.XlFileFormat.xlWorkbookNormal,
                            null, null, false, false,
                            COMExcel.XlSaveAsAccessMode.xlExclusive,
                            false, false, false, false, false);
            exApp.Quit();
            MessageBox.Show("Lưu thành công tại : D:\\BC_XUATHANG_DUOC.xls", "Thông báo");

            FileInfo fi = new FileInfo("D:\\BC_XUATHANG_DUOC.xls");
            if (fi.Exists)
            {
                System.Diagnostics.Process.Start(@"D:\\BC_XUATHANG_DUOC.xls");
            }
            else
            {
                //
            }
            #endregion
            this.prgXuatExcel.Visible = false;
            this.lblText.Visible = false;

            System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
            }
        }

        public override void btnExportReport_Click(object sender, EventArgs e)
        {
            base.btnExportReport_Click(sender, e);
            SqlParameter[] parm = new SqlParameter[3];

            parm[0] = new SqlParameter("@tungay", SqlDbType.DateTime, 8);
            parm[0].Value = dateFrom.Value;
            parm[1] = new SqlParameter("@denngay", SqlDbType.DateTime, 8);
            parm[1].Value = dateTo.Value;
            SqlHelper.ExecuteNonQuery(ConfigurationManager.AppSettings["Main.ConnectionString"],
                CommandType.StoredProcedure,
                "zD_BC_TH_XUATHANG",
                parm);

            string sql = "select mavach,tenvt,sum(soluongban-isnull(tralai,0)) as slban,sum(tongban-isnull(thanhTienTraLai,0)) as tb,dvt ";
            sql += " from zD_BC_TongHopBanHang";
            sql += " where soluongban >0 and ngaylap between '" + dateFrom.Value.ToString("MM/dd/yyyy HH:mm:ss") + "' and '" + dateTo.Value.ToString("MM/dd/yyyy HH:mm:ss") + "' and loaiphieu <>4";
            sql += " group by mavach,tenvt,dvt";

            if (cboBieuMau.SelectedIndex == 1)
            {
                sql = "select mavach,dongianhap,dongiaban,tenvt,sum(soluongban-isnull(tralai,0)) as slban,sum(tongban-isnull(thanhTienTraLai,0)) as tb,dvt,sum((dongiaban-dongianhap)*soluongban) as lai,(dongiaban-dongianhap)/dongianhap *100 as laixuat ";
                sql += " from zD_BC_TongHopBanHang";
                sql += " where soluongban >0 and ngaylap between '" + dateFrom.Value.ToString("MM/dd/yyyy HH:mm:ss") + "' and '" + dateTo.Value.ToString("MM/dd/yyyy HH:mm:ss") + "' and loaiphieu <>4";
                sql += " group by mavach,tenvt,dvt,dongianhap,dongiaban";
            }
            else if (cboBieuMau.SelectedIndex == 2)
            {
                sql = "select mahanghoa,tenvt,sum(soluongban-isnull(tralai,0)) as slban,sum(tongban-isnull(thanhTienTraLai,0)) as tb,dvt ";
                sql += " from zD_BC_TongHopBanHang";
                sql += " where soluongban >0 and ngaylap between '" + dateFrom.Value.ToString("MM/dd/yyyy HH:mm:ss") + "' and '" + dateTo.Value.ToString("MM/dd/yyyy HH:mm:ss") + "' and loaiphieu <>4";
                sql += " group by mahanghoa,tenvt,dvt";
            }

            DataTable dt = new DataTable();
            dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql).Tables[0];
            frmShowReport frm = new frmShowReport(1);
            frmShowReport._dt = dt;
            frmShowReport.strC = frmShowReport.BC.TKXUATHANG;
            if (cboBieuMau.SelectedIndex == 1)
            {
                frmShowReport.strC = frmShowReport.BC.TKXUATHANG;
            }
            else if (cboBieuMau.SelectedIndex == 2)
            {
                frmShowReport.strC = frmShowReport.BC.TKXUATHANG_THANG;
            }

            frmShowReport._tungay = dateFrom.Value.ToString("dd/MM/yyyy");
            frmShowReport._denngay = dateTo.Value.ToString("dd/MM/yyyy");
            float tongtien = 0;
            float tonglai=0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                tongtien += float.Parse(dt.Rows[i]["tb"].ToString());
                tonglai += float.Parse(dt.Rows[i]["lai"].ToString());
            }
            frmShowReport._thanhtien = String.Format("{0:0,0}", tongtien);
            frmShowReport._tonglai = String.Format("{0:0,0}", tonglai);
            frm.Show();
        }
    }
}
